<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="java.sql.*, support.*, javax.sql.*, javax.naming.*, java.util.*"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<link href="main.css" rel="stylesheet" type="text/css">
<title>Discipline Analytics</title>
</head>
<body>
<h1>Discipline Analytics</h1>
<table class="analytics">
	<tr>
		<th>Discipline</th>
		<th>Applicants</th>
	</tr>
<%
	Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
    	// Obtain the environment naming context
    	Context initCtx = new InitialContext();
    	// Look up the data source
    	DataSource ds = (DataSource)initCtx.lookup("java:comp/env/jdbc/CSE135");
    	// Allocate and use a connection from the pool
    	conn = ds.getConnection();
    	stmt = conn.createStatement();
    	String sql = "SELECT m.m_id, m.major, COUNT(DISTINCT a.id) FROM majors AS m " +
    			"LEFT JOIN degrees AS d ON m.m_id=d.major " +
    			"LEFT JOIN applicants AS a ON d.applicant = a.id " +
    			"GROUP BY m.m_id, m.major ORDER BY m_id";
        // Create the statement
		rs = stmt.executeQuery(sql);
	    int major_id;
	    String major_name;
	    int count;
		while(rs.next())
		{
			major_id = rs.getInt(1);
			major_name = rs.getString(2);
			count = rs.getInt(3);
		%>
		<tr>
			<td><%= major_name%></td>
			<td class="mid">
			<% if (count > 0) { %>
				<a href=applications.jsp?major_id=<%= major_id %>><%= count %></a>
			<% } else { %>
				<%= count %>
			<% } %>
			</td>
		</tr>
		<%
		}
		rs.close();
		stmt.close();
    	conn.close();
     }
    catch (SQLException e){
    	e.getStackTrace();
    }
%>
</table>
</body>
</html>